<html>
  <head><title>SP Objects</title>
    <script src="codepress/codepress.js" type="text/javascript"></script> 
    <link rel="stylesheet" href="style.css" type="text/css" media="all" />
  </head>
  <body>

    <h1>Procedure Object: F_QT_CTRCT_ACTIVE.SQL</h1>
    <p><b>Author:</b> Will Chang</p>
    <p><b>Date:</b> Feb 14, 2007</p>
    <p><b>Abstract:</b></p>
    <p></b></p>
    <p><b>Sample Calls:</b></p>
    <p></b></p>
    <p></p>
    
    
    <h3>Tables:</h3>
    <ul>
      
        <li><b>ctrct_terms</b></li>
      
    </ul>
    
    
    
    
    
    
    
    
    <h3>Source code:</h3>
    <div id="SQLsrc" >
<em>DROP</em>&nbsp;SPECIFIC&nbsp;<b>FUNCTION</b>&nbsp;EBIZ1.F_QT_CTRCT_ACTIVE<br />@<br /><br /><em>CREATE</em>&nbsp;<b>FUNCTION</b>&nbsp;EBIZ1.F_QT_CTRCT_ACTIVE(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;piSapCtrctNum&nbsp;&nbsp;&nbsp;&nbsp;<u>VARCHAR</u>(10)<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,piLineOfBusCode&nbsp;<u>VARCHAR</u>(4))<br /><b>RETURNS</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>SMALLINT</u><br />&nbsp;&nbsp;&nbsp;&nbsp;SPECIFIC&nbsp;F_QT_CTRCT_ACTIVE<br />&nbsp;&nbsp;&nbsp;&nbsp;<b>LANGUAGE</b>&nbsp;<b>SQL</b><br />&nbsp;&nbsp;&nbsp;&nbsp;<b>NOT</b>&nbsp;DETERMINISTIC<br />&nbsp;&nbsp;&nbsp;&nbsp;READS&nbsp;<b>SQL</b>&nbsp;DATA<br />&nbsp;&nbsp;&nbsp;&nbsp;<b>CALLED</b>&nbsp;<b>ON</b>&nbsp;<b>NULL</b>&nbsp;<b>INPUT</b><br />&nbsp;&nbsp;&nbsp;&nbsp;<b>NO</b>&nbsp;EXTERNAL&nbsp;ACTION<br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;<b>RETURN</b></i><br /><i>-----------------------------------------------------------------------------------</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;<b>Function</b>&nbsp;Name:&nbsp;F_QT_CTRCT_ACTIVE</i><br /><i>--&nbsp;&nbsp;Specific&nbsp;Name:&nbsp;F_QT_CTRCT_ACTIVE</i><br /><i>--&nbsp;&nbsp;File&nbsp;Name:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;F_QT_CTRCT_ACTIVE.<b>SQL</b></i><br /><i>--</i><br /><i>--&nbsp;&nbsp;Author:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Will&nbsp;Chang</i><br /><i>--&nbsp;&nbsp;<u>Date</u>:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Feb&nbsp;14,&nbsp;2007</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;Abstract</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;Determine&nbsp;<b>if</b>&nbsp;the&nbsp;given&nbsp;ctrct_num&nbsp;<b>is</b>&nbsp;an&nbsp;active&nbsp;contract&nbsp;<b>or</b>&nbsp;<b>not</b>.</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;Sample&nbsp;Call</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;<b>values</b>(&nbsp;EBIZ1.F_QT_CTRCT_ACTIVE&nbsp;(<s>'00000C1234'</s>,<s>'PA'</s>)&nbsp;)</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;<b>values</b>(&nbsp;EBIZ1.F_QT_CTRCT_ACTIVE&nbsp;(<s>'00000C1278'</s>,<s>'FCT'</s>)&nbsp;)</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;<b>values</b>(&nbsp;EBIZ1.F_QT_CTRCT_ACTIVE&nbsp;(<s>'00000C5678'</s>,<s>'PA'</s>)&nbsp;)</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;<b>values</b>(&nbsp;EBIZ1.F_QT_CTRCT_ACTIVE&nbsp;(<s>'0000127441'</s>,<s>'FC'</s>)&nbsp;)</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;<b>values</b>(&nbsp;EBIZ1.F_QT_CTRCT_ACTIVE&nbsp;(<s>'0000127441'</s>,<s>'FCT'</s>)&nbsp;)</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;<b>Return</b></i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;(i.e.&nbsp;active&nbsp;&nbsp;contract)</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;(i.e.&nbsp;inactive&nbsp;contract)</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;(i.e.&nbsp;<b>for</b>&nbsp;any&nbsp;errors)</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;Maintenance&nbsp;Log</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;who&nbsp;&nbsp;<u>date</u>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b><em>comment</em></b></i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;---&nbsp;&nbsp;----------&nbsp;&nbsp;------------------------------------------------------------</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;Will&nbsp;&nbsp;02/14/2007&nbsp;&nbsp;Initial&nbsp;Version</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;Will&nbsp;&nbsp;02/25/2007&nbsp;&nbsp;Changed&nbsp;name&nbsp;<b>from</b>&nbsp;F_QTE_CTRCT_ACTIVE&nbsp;<b>to</b>&nbsp;F_QT_CTRCT_ACTIVE</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;Will&nbsp;&nbsp;03/07/2007&nbsp;&nbsp;<b>If</b>&nbsp;LOB&nbsp;=&nbsp;<s>'PA'</s>&nbsp;<b>THEN</b>&nbsp;ctrct_terms_stat_code&nbsp;=&nbsp;<s>'E0011'</s>,&nbsp;<b>and</b>&nbsp;don't&nbsp;<b>check</b>&nbsp;ct.ctrct_end_date</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ctrct_start_date&nbsp;<b>and</b>&nbsp;ctrct_end_date&nbsp;could&nbsp;be&nbsp;<b>NULL</b></i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;WGO&nbsp;&nbsp;&nbsp;03/20/07&nbsp;&nbsp;&nbsp;&nbsp;Remove&nbsp;the&nbsp;IEMS&nbsp;logic&nbsp;<b>and</b>&nbsp;<b>add</b>&nbsp;the&nbsp;FCT&nbsp;ctrct&nbsp;active&nbsp;<b>check</b></i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;Will&nbsp;&nbsp;03/21/07&nbsp;&nbsp;&nbsp;&nbsp;<b>Only</b>&nbsp;<b>check</b>&nbsp;PA&nbsp;<b>or</b>&nbsp;FCT</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;Bill&nbsp;&nbsp;03/21/07&nbsp;&nbsp;&nbsp;&nbsp;Remove&nbsp;<b>all</b>&nbsp;<b>references</b>&nbsp;<b>to</b>&nbsp;IEMS,&nbsp;<s>'EM'</s>&nbsp;-&nbsp;since&nbsp;the&nbsp;IEMS&nbsp;<u>line</u>&nbsp;<b>of</b>&nbsp;business&nbsp;has&nbsp;been&nbsp;descoped&nbsp;<b>for</b>&nbsp;DSW&nbsp;9.1</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;AV&nbsp;&nbsp;&nbsp;&nbsp;05/10/07&nbsp;&nbsp;&nbsp;&nbsp;<b>check</b>&nbsp;<b>for</b>&nbsp;<s>'FCT'</s>&nbsp;<b>as</b>&nbsp;well&nbsp;<b>as</b>&nbsp;<s>'FC'</s></i><br /><i>-----------------------------------------------------------------------------------</i><br /><em>BEGIN</em>&nbsp;ATOMIC<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;vLineOfBusCode&nbsp;<u>VARCHAR</u>(4);<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;<b>IF</b>&nbsp;piLineOfBusCode&nbsp;=&nbsp;<s>'FCT'</s>&nbsp;<b>THEN</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;vLineOfBusCode&nbsp;=&nbsp;<s>'FC'</s>;<br />&nbsp;&nbsp;&nbsp;&nbsp;<b>ELSE</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;vLineOfBusCode&nbsp;=&nbsp;piLineOfBusCode;<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>IF</b>;<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;<b>IF</b>&nbsp;EXISTS&nbsp;(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SELECT</em>&nbsp;1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>FROM</b>&nbsp;&nbsp;&nbsp;SODS2.ctrct_terms&nbsp;ct<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHERE</b>&nbsp;&nbsp;ct.sap_ctrct_num&nbsp;=&nbsp;piSapCtrctNum<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>AND</b>&nbsp;&nbsp;ct.line_of_bus_code&nbsp;=&nbsp;vLineOfBusCode<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>AND</b>&nbsp;&nbsp;(<b>CASE</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHEN</b>&nbsp;&nbsp;&nbsp;vLineOfBusCode&nbsp;=<s>'FC'</s><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>AND</b>&nbsp;ct.sap_sales_doc_type_code&nbsp;<b>IN</b>&nbsp;(<s>'ZFCQ'</s>,<s>'ZFCV'</s>)<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>THEN</b>&nbsp;&nbsp;&nbsp;1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHEN</b>&nbsp;&nbsp;&nbsp;vLineOfBusCode&nbsp;=<s>'PA'</s><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>THEN</b>&nbsp;&nbsp;&nbsp;1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>ELSE</b>&nbsp;&nbsp;&nbsp;0<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>)&nbsp;=&nbsp;1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>AND</b>&nbsp;&nbsp;ct.ctrct_terms_stat_code&nbsp;=&nbsp;<s>'E0011'</s><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>AND</b>&nbsp;&nbsp;ct.ctrct_termntn_date&nbsp;<b>IS</b>&nbsp;<b>NULL</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>AND</b>&nbsp;&nbsp;(ct.ctrct_start_date&nbsp;<b>IS</b>&nbsp;<b>NULL</b>&nbsp;<b>OR</b>&nbsp;ct.ctrct_start_date&nbsp;<=&nbsp;current&nbsp;<u>date</u>)<br />&nbsp;&nbsp;&nbsp;&nbsp;)<br />&nbsp;&nbsp;&nbsp;&nbsp;<b>THEN</b>&nbsp;<b>RETURN</b>&nbsp;1;<br />&nbsp;&nbsp;&nbsp;&nbsp;<b>ELSE</b>&nbsp;<b>RETURN</b>&nbsp;0;<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>IF</b>;<br /><br /><em>END</em><br />@<br />CALL&nbsp;DTOL.G_FN_EXEC_GRP(<s>'EBIZ1'</s>,&nbsp;<s>'F_QT_CTRCT_ACTIVE'</s>,&nbsp;<s>'public'</s>)<br />@<br />
    </div> 
  </body>
</html>
